package dao;

import java.sql.*;

import bean.Customer;
import bean.GradeDesign;
import bean.Score;
import conn.Conn;

public class StudentSql {

	//查询是否有该学号密码的学生
	public static boolean isStudent(String ID, String Password){

		Connection conn = Conn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		String type = null;
		try {
			stmt = conn.createStatement();
			String sql = "select * from customer where ID =" + "'" + ID+ "'" + " and Password=" + "'" + Password + "'";
			rs = stmt.executeQuery(sql);
			if(rs.next()) {
				type = rs.getString("Type");
				return type.equals("student");
			}
			return false;
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			Conn.release(conn, stmt, rs);
		}
		return false;
	}

	//根据学生的学号查询学生姓名
	public static String getName(String ID) {

		Connection conn = Conn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			String sql = "select Name from customer where ID = " + "'" + ID +"'";
			rs = stmt.executeQuery(sql);
			if(rs.next()) {
				return rs.getString("Name");
			}
			return null;
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			Conn.release(conn, stmt, rs);
		}
		return null;
	}

	//查询个人信息
	public static Customer getCustomerByID(String ID) {
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			String sql = "select * from customer where ID = "+"'" + ID + "'";
			rs = stmt.executeQuery(sql);
			Customer customer = null;
			if(rs.next()) {
				String Name = rs.getString("Name");
				String Email = rs.getString("Email");
				String Sex = rs.getString("Sex");
				String Phone = rs.getString("Phone");
				String Type = rs.getString("Type");
				customer = new Customer(Name, ID);
				customer.setSex(Sex);
				customer.setEmail(Email);
				customer.setPhone(Phone);
				customer.setType(Type);
				return customer;
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			Conn.release(conn, stmt, rs);
		}
		return null;
	}

	//更新个人信息
	public static boolean updateCustomer(String ID, String Email,String Sex,String Phone){
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			String sql = "update customer set Email = '" + Email + "',Sex = '" +  Sex + "',Phone = '" + Phone + "'where ID = '"  + ID + "'";
			stmt.executeUpdate(sql);
			return true;
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			Conn.release(conn, stmt);
		}
		return false;
	}

	//更新密码
	public static boolean updatePassword(String ID, String Password){

		Connection conn = Conn.getConnection();
		Statement stmt = null;
		int a = 0;
		try {
			stmt = conn.createStatement();
			String sql = "update customer set password = " + "'" + Password + "'" +  " where ID = " + "'" + ID + "'";
			a = stmt.executeUpdate(sql);
			return a > 0;
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			Conn.release(conn, stmt);
		}
		return false;
	}

	//学生查询成绩
	public static Score selectGrade(String ID) {

		Connection conn = Conn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			String sql = "select * from dbo.score where ID = " + "'" + ID +"'";
			rs = stmt.executeQuery(sql);
			if(rs.next()) {
				String Name = rs.getString("Name");
				String Title = rs.getString("Title");
				String T_Nmae = rs.getString("T_Name");
				String Score = rs.getString("Score");
				String Grade = rs.getString("Grade");
				return new Score(ID,Name,Title,T_Nmae,Score,Grade);

			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			Conn.release(conn, stmt, rs);
		}
		return null;
	}

	//学生查询毕业设计相关信息是否存在
	public static boolean isDesign(String ID) {
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			String sql = "select * from dbo.grade_design where ID = " + "'" + ID + "'";
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Conn.release(conn, stmt, rs);
		}
		return false;
	}

	//学生查询毕业设计相关信息
	public static GradeDesign selectDesign(String ID) {
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		GradeDesign gradeDesign = new GradeDesign();
		try {
			stmt = conn.createStatement();
			String sql = "select * from dbo.grade_design where ID = " + "'" + ID + "'";
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				gradeDesign.setID(rs.getString("ID"));
				gradeDesign.setName(rs.getString("Name"));
				gradeDesign.setTitle(rs.getString("Title"));
				gradeDesign.setT_Name(rs.getString("T_Name"));
				gradeDesign.setDepart(rs.getString("Depart"));
				gradeDesign.setMajor(rs.getString("Major"));
				gradeDesign.setPhone(rs.getString("Phone"));
				gradeDesign.setRWS(rs.getString("RWS"));
				gradeDesign.setKTBG(rs.getString("KTBG"));
				gradeDesign.setBYLW(rs.getString("BYLW"));
				gradeDesign.setCODE(rs.getString("CODE"));
				return gradeDesign;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Conn.release(conn, stmt, rs);
		}
		return null;
	}

	//学生查询毕业设计相关信息
	public static GradeDesign selectDesignInCustomer(String ID) {
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		GradeDesign gradeDesign = new GradeDesign();
		try {
			stmt = conn.createStatement();
			String sql = "select * from dbo.customer where ID = " + "'" + ID + "'";
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				String Name = rs.getString("Name");
				gradeDesign.setID(ID);
				gradeDesign.setName(Name);
				return gradeDesign;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Conn.release(conn, stmt, rs);
		}
		return null;
	}

	//更新毕业设计相关信息
	public static boolean updataDesign(String ID,String Depart,String Major,String Phone,String RWS,String KTBG,String BYLW,String CODE) {
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		int a = 0;
		try {
			stmt = conn.createStatement();
			String sql = "update grade_design set Depart = '" + Depart + "',Major = '" +  Major + "',Phone = '" + Phone + "'where ID = '"  + ID + "'";
			if(RWS != null){
				stmt.executeUpdate("update grade_design set RWS = '" + RWS + "'where ID = '"  + ID + "'");
			}else if (KTBG != null){
				stmt.executeUpdate("update grade_design set KTBG = '" + KTBG + "'where ID = '"  + ID + "'");
			}else if (BYLW != null){
				stmt.executeUpdate("update grade_design set BYLW = '" + BYLW + "'where ID = '"  + ID + "'");
			}else if (CODE != null){
				stmt.executeUpdate("update grade_design set CODE = '" + CODE + "'where ID = '"  + ID + "'");
			}
			a = stmt.executeUpdate(sql);
			return a > 0;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Conn.release(conn, stmt);
		}
		return false;
	}

	//插入毕业设计相关信息
	public static boolean inseDesign(String ID,String Name,String Depart,String Major,String Title,String Phone,String RWS,String KTBG,String BYLW,String CODE) {
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		int a = 0;
		try {
			stmt = conn.createStatement();
			String sql = "insert into grade_design(ID, Name, Depart,Major,Title, Phone, RWS,KTBG ,BYLW,CODE ) values (" + "'" + ID + "'" + "," + " '" + Name + "'" + "," + " '" + Depart + "'" + "," + " '" + Major + "'" + "," + " '" + Title + "'" + "," + " '" + Phone + "'" + "," + " '" + RWS + "'" + "," + " '" + KTBG + "'" + "," + " '" + BYLW + "'" + "," + " '" + CODE + "'" + ")";
			a = stmt.executeUpdate(sql);
			return a > 0;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Conn.release(conn, stmt);
		}
		return false;
	}
}
